using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using twistmachinev2.Entity;

namespace twistmachinev2.Business
{
    /// <summary>
    /// Excel模板生成器
    /// </summary>
    public static class ExcelTemplate
    {
        /// <summary>
        /// 创建Excel导入模板
        /// </summary>
        /// <param name="filePath">保存路径</param>
        public static void CreateImportTemplate(string filePath)
        {
            IWorkbook workbook;
            string extension = Path.GetExtension(filePath).ToLower();
            
            if (extension == ".xlsx")
            {
                workbook = new XSSFWorkbook();
            }
            else if (extension == ".xls")
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                throw new ArgumentException("不支持的文件格式，请使用.xlsx或.xls格式");
            }

            ISheet sheet = workbook.CreateSheet("OPC UA节点配置");

            // 创建标题行
            IRow headerRow = sheet.CreateRow(0);
            string[] headers = {
                "NodeId", "Name", "Description", "DataType", "AccessLevel",
                "ScanRate", "PropertyName", "ObjectName", "Group", "Sort",
                "Remark", "IsSubscribed", "IsEnabled", "WriteFunction"
            };

            // 创建标题样式
            ICellStyle headerStyle = workbook.CreateCellStyle();
            IFont headerFont = workbook.CreateFont();
            headerFont.IsBold = true;
            headerFont.FontHeightInPoints = 12;
            headerStyle.SetFont(headerFont);
            headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
            headerStyle.FillPattern = FillPattern.SolidForeground;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderLeft = BorderStyle.Thin;
            headerStyle.BorderRight = BorderStyle.Thin;
            headerStyle.BorderTop = BorderStyle.Thin;

            // 设置标题
            for (int i = 0; i < headers.Length; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
                sheet.SetColumnWidth(i, 4000); // 设置列宽
            }

            // 添加示例数据行
            IRow exampleRow = sheet.CreateRow(1);
            string[] exampleData = {
                "ns=2;s=Device1.Temperature",
                "设备1温度",
                "设备1的温度传感器读数",
                "Double",
                "CurrentRead",
                "1000",
                "Temperature",
                "Device1",
                "温度监控",
                "1",
                "温度传感器",
                "true",
                "true",
                "0"
            };

            for (int i = 0; i < exampleData.Length; i++)
            {
                ICell cell = exampleRow.CreateCell(i);
                cell.SetCellValue(exampleData[i]);
            }

            // 添加说明工作表
            ISheet instructionSheet = workbook.CreateSheet("导入说明");
            CreateInstructionSheet(instructionSheet, workbook);

            // 保存文件
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }

            workbook.Close();
        }

        /// <summary>
        /// 创建说明工作表
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="workbook">工作簿</param>
        private static void CreateInstructionSheet(ISheet sheet, IWorkbook workbook)
        {
            // 创建标题样式
            ICellStyle titleStyle = workbook.CreateCellStyle();
            IFont titleFont = workbook.CreateFont();
            titleFont.IsBold = true;
            titleFont.FontHeightInPoints = 14;
            titleStyle.SetFont(titleFont);

            // 创建内容样式
            ICellStyle contentStyle = workbook.CreateCellStyle();
            contentStyle.WrapText = true;

            int rowIndex = 0;

            // 标题
            IRow titleRow = sheet.CreateRow(rowIndex++);
            ICell titleCell = titleRow.CreateCell(0);
            titleCell.SetCellValue("OPC UA节点配置导入说明");
            titleCell.CellStyle = titleStyle;

            rowIndex++; // 空行

            // 字段说明
            string[] instructions = {
                "字段说明：",
                "NodeId: OPC UA节点标识符，必填项，格式如：ns=2;s=Device1.Temperature",
                "Name: 节点显示名称，必填项",
                "Description: 节点描述信息，可选",
                "DataType: 数据类型，如：String、Double、Boolean等，默认为String",
                "AccessLevel: 访问级别，如：CurrentRead、CurrentWrite等，默认为CurrentRead",
                "ScanRate: 扫描频率（毫秒），默认为1000",
                "PropertyName: 属性名称，可选",
                "ObjectName: 对象名称，可选",
                "Group: 分组名称，可选",
                "Sort: 排序号，数字类型，默认为1",
                "Remark: 备注信息，可选",
                "IsSubscribed: 是否订阅，true/false或是/否，默认为true",
                "IsEnabled: 是否启用，true/false或是/否，默认为true",
                "WriteFunction: 写入功能，数字类型，默认为0",
                "",
                "注意事项：",
                "1. NodeId字段为必填项，不能为空",
                "2. 如果Name字段为空，将使用NodeId作为名称",
                "3. 布尔类型字段支持true/false或是/否格式",
                "4. 数字类型字段请确保格式正确",
                "5. 导入时会根据NodeId判断是新增还是更新数据",
                "6. 建议先备份现有数据再进行导入操作"
            };

            foreach (string instruction in instructions)
            {
                IRow row = sheet.CreateRow(rowIndex++);
                ICell cell = row.CreateCell(0);
                cell.SetCellValue(instruction);
                cell.CellStyle = contentStyle;
            }

            // 设置列宽
            sheet.SetColumnWidth(0, 15000);
        }

        /// <summary>
        /// 导出现有数据到Excel
        /// </summary>
        /// <param name="filePath">保存路径</param>
        /// <param name="nodes">节点数据</param>
        public static void ExportToExcel(string filePath, List<OpcUaNodeConfigEntity> nodes)
        {
            IWorkbook workbook;
            string extension = Path.GetExtension(filePath).ToLower();
            
            if (extension == ".xlsx")
            {
                workbook = new XSSFWorkbook();
            }
            else if (extension == ".xls")
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                throw new ArgumentException("不支持的文件格式，请使用.xlsx或.xls格式");
            }

            ISheet sheet = workbook.CreateSheet("OPC UA节点配置");

            // 创建标题行
            IRow headerRow = sheet.CreateRow(0);
            string[] headers = {
                "NodeId", "Name", "Description", "DataType", "AccessLevel",
                "ScanRate", "PropertyName", "ObjectName", "Group", "Sort",
                "Remark", "IsSubscribed", "IsEnabled", "WriteFunction"
            };

            // 创建标题样式
            ICellStyle headerStyle = workbook.CreateCellStyle();
            IFont headerFont = workbook.CreateFont();
            headerFont.IsBold = true;
            headerFont.FontHeightInPoints = 12;
            headerStyle.SetFont(headerFont);
            headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
            headerStyle.FillPattern = FillPattern.SolidForeground;

            // 设置标题
            for (int i = 0; i < headers.Length; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
                sheet.SetColumnWidth(i, 4000);
            }

            // 添加数据行
            for (int i = 0; i < nodes.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                var node = nodes[i];

                row.CreateCell(0).SetCellValue(node.NodeId ?? "");
                row.CreateCell(1).SetCellValue(node.Name ?? "");
                row.CreateCell(2).SetCellValue(node.Description ?? "");
                row.CreateCell(3).SetCellValue(node.DataType ?? "");
                row.CreateCell(4).SetCellValue(node.AccessLevel ?? "");
                row.CreateCell(5).SetCellValue(node.ScanRate);
                row.CreateCell(6).SetCellValue(node.PropertyName ?? "");
                row.CreateCell(7).SetCellValue(node.ObjectName ?? "");
                row.CreateCell(8).SetCellValue(node.Group ?? "");
                row.CreateCell(9).SetCellValue(node.Sort);
                row.CreateCell(10).SetCellValue(node.Remark ?? "");
                row.CreateCell(11).SetCellValue(node.IsSubscribed ? "是" : "否");
                row.CreateCell(12).SetCellValue(node.IsEnabled ? "是" : "否");
                row.CreateCell(13).SetCellValue(node.WriteFunction);
            }

            // 保存文件
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }

            workbook.Close();
        }
    }
}